﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel ;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Threading;
using System.Diagnostics;

namespace TestExcel
{
     
    class Program
    {
        // Reading data from range 
        // Much faster than reading cell by cell
        //the values are stored in the Value2 property of range object
        static void Main(string[] args)
        {
            excelize();

        }

        private static void ReleaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                Console.WriteLine("Exception" + ex.Message);
                //MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
                Thread.Sleep(30);
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
        private static void excelize()
        {
            string filePath = @"C:\Users\emanuel\Desktop\";
            string fileName = @"t.xlsx";    
            object[,] values = null;
            Excel.Application app = null;
            Excel.Workbooks workbooks = null;
            Excel.Workbook workbook = null;
            Excel.Sheets sheets = null;
            Excel.Worksheet sheet = null;
            Excel.Range range = null; // much faster by using a range than reading the cells individually

            try
            {
                app = new Excel.Application();
                app.Visible = true;
                workbooks = app.Workbooks;
                workbook = workbooks.Open(filePath + fileName,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Excel.XlPlatform.xlWindows,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value);

                sheets = workbook.Worksheets;
                sheet = sheets[1];
                range = sheet.get_Range("A1", Missing.Value);
                range = range.get_End(Excel.XlDirection.xlToRight);
                range = range.get_End(Excel.XlDirection.xlDown); //put your hands on the range A1 to bottom right corner

                object misValue = System.Reflection.Missing.Value;
                workbook.Close(true,misValue,misValue);
                //string downAddress = range.get_Address(
                //    false, false, Excel.XlReferenceStyle.xlA1,
                //    Type.Missing, Type.Missing);
                //range = sheet.get_Range("A1", downAddress); //entire range from A1 to downAddress

                //values = (object[,])range.Value2;

                //Console.WriteLine(values.GetLength(0));
                //Console.WriteLine(values.GetLength(1));

                //for (int i = 1; i <= values.GetLength(0); i++)
                //    for (int j = 1; j <= values.GetLength(1); j++ )
                //        Console.WriteLine(values[i, j]);    
            }

            catch (Exception exp)
            {
                Console.WriteLine(exp.Message.ToString());
            }
            finally
            {
                TryToKillXlApp(app);

            }
            Console.ReadLine();
        }

        static void TryToKillXlApp(Excel.Application xlApp)
        {
            if (xlApp != null)
            {
                int excelProcessId = 0;
                GetWindowThreadProcessId(xlApp.Hwnd, out excelProcessId);
                Process p = Process.GetProcessById(excelProcessId);
                p.Kill();
                xlApp = null;
            }
        }

        [DllImport("user32.dll")]
        static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);
    }
}
